This is shown in table 1.
Table 1; Calculations to create the index
1
2
3
4
Month
Average
Year 1
Year 2
Year 3
Year 4
1
39,600
0.45
1.14
1.51
0.90
2
37,080
0.53
1.25
0.83
1.38
3
30,000
0.52
0.74
1.59
1.15
4
59,210
0.91
0.70
1.25
1.15
5
64,375
1.29
0.71
0.94
1.06
6
57,750
1.26
0.72
0.96
1.06
7
47,370
1.17
0.84
0.68
1.32
8
56,638
1.01
1.13
0.68
1.17
9
29,855
0.52
1.59
0.84
1.05
10
39,638
0.70
1.09
1.29
0.92
11
27,323
0.78
1.44
1.16
0.61
12
19,350
0.88
0.53
1.61
0.98
With the creation of the index for each moth, this may then be used to assess the most likely demand. The most appropriate method is the use of the least square regression. This uses the data from the previous years and places them on a graph, drawing a straight line through the points so it has the least distance from the different points. The future forecasts are assumed to be on this line. The equation for the line can be used to calculate forecasts. The graph for month 1 is shown below in figure 1.
Figure 2; Graph for Month 1 demand
The line shows the general trend and is the closest the line can be drawn to all...
In Excel it is possible for the program to calculate the slope of the lie with an equation, for month 1 the equation is y = 0.1697x+0.5758, x will be the period for which the forecast (y) is being calculated. This gives a result of 1.42. The index level form month 1 is forecast at 1.42, this may then be converted to the actual amount by multiplying the index by the base line (Shmueli, 2012). This can be repeated for each month
. This gives the forecast demand shown in table 2.
Table 2; Forecast demand for year 5
Month
Forecast for year 5 (index)
Mean for the month
Actual units forecast
1
1.42
39,600
56,400
2
1.53
37,080
56,720
3
1.68
30,000
50,450
4
1.32
59,210
78,145
5
0.88
64,375
56,600
6
0.90
57,750
52,250
7
1.07
47,370
50,790
8
1.01
56,638
57,125
9
1.21
29,855
36,210
10
1.22
39,638
48,300
11
0.81
27,323
21,995
12
1.34
19,350
25,900
Total
590,885
Now the business has a total breakdown of the forecast for each month and the total for the year. It is unlikely to be totally accurate, as the demand lines were not straight and showed some volatility, but will give a good basis for forecasting.
References
O'Connell, Richard; Koehler, Anne, (2004), Forecasting, Time Series, and Regression, South Western Collage Publishers
Shmueli, G, (2012), Practical Time Series Forecasting: A Hands-on Guide, CreateSpace Independent Publishing Platform
The forecast function in Excel prevents…
